import re
import pandas as pd
import pymysql
import ast
from sqlalchemy import create_engine
if __name__ == '__main__':
    job_name = "merged_data_annual_salary"
    company_name="merged_data_company"
    job_df = pd.read_csv(f"{job_name}.csv")
    selected_columns=pd.read_csv(f"{company_name}.csv")

    cols_mapping = {
        'job_id': 'jobId',
        'company_id': 'companyId',
        'company_name': 'companyName',
        'job_title': 'name',
        'hr_name': 'hr',
        'job_salary': 'salary',
        'annual_salary': 'salaryYear',
        'tags': 'tags',
        'job_description': 'description',
        '学历': 'degree',
        '资历': 'experience',
        'company_brief_address': 'location',
        'deleted': 'deleted',
        '角色定位': 'workRole',
        '技术要求': 'workRequirements',
        '工作领域': 'workField',
        '工作经验': 'workExperience',
        'year': 'workYear'
    }

    df_renamed = job_df.rename(columns=cols_mapping)

    database_url = 'mysql+pymysql://qwy:190601@localhost:3306/jobs'  # 数据库URL，根据实际情况修改

    # 创建数据库引擎
    engine = create_engine(database_url)

    df_renamed.to_sql(
        name='job',
        con=engine,
        if_exists='append',
        index=False,
    )


    cols_mapping = {
        'company_id': 'companyID',
        'company_name': 'name',
        'phone': 'phone',
        'company_intro': 'introduction',
        'company_status': 'status',
        'company_size': 'size',
        'company_detailed_address': 'address',
        'company_type': 'companyType',
    }
    df_renamed = selected_columns.rename(columns=cols_mapping)

    df_renamed.to_sql(
        name='company',
        con=engine,
        if_exists='append',
        index=False,
    )